PROBLEM STATEMENT¶
The dataset provided to you has data for several websites owned by the same company and they are asking for your help for what should be their approach to set reserve prices and what is the range for reserve prices they should be setting for July. The data is only of the actual revenue generation and not at bid level. The dataset has the following columns:
- Date
- site_id : each id denotes a different website
- ad_type_id : each id denotes a different ad_type. These can be display ads , video ads, text ads etc
- geo_id : each id denotes a different country. our maximum traffic is from english speaking countries
- device_category_id : each id denoted a different device_category like desktop , mobile, tablet
- advertiser_id: each id denotes a different bidder in the auction
- order_id : can be ignored
- line_item_type_id : can be ignored
- os_id : each id denotes a different operating system for mobile device category only (android , ios etc) . for all other device categories, os_id will correspond to not_mobile
- integration_type_id : it describes how the demand partner is setup within a publisher's ecosystem - can be adserver (running through the publisher adserver) or hardcoded
- monetization_channel_id : it describes the mode through which demand partner integrates with a particular publisher - it can be header bidding (running via prebid.js), dynamic allocation, exchange bidding, direct etc
- ad_unit_id - each id denotes a different ad unit (one page can have more than one ad units)
- total_impressions - total number of eligible impressions
- total_revenue - measurement column measuring the revenue for the particular set of dimensions
- viewable_impressions - Number of impressions on the site that were viewable out of all measurable impressions. A display ad is counted as viewable if at least 50% of its area was displayed on screen for at least one second
- measurable_impressions - Impressions that were measurable by Active View out of the total number of eligible impressions. This value should generally be close to 100%. For example, an impression that is rendering in a cross-domain iframe may not be measurable.
- Revenue_share_percent - not every advertiser gives all the revenue to the publisher. They charge a certain share for the services they provide. This captures the fraction of revenue that will actually reach the publishers pocket.
QUESTIONS –
- The person compiling the data recorded some inconsistencies within the numbers. Can you identify and categorize these inconsistencies and errors? (ex: revenue_share_percent cannot be more than 1 [100%])
- For each site, which advertiser has the highest CPM, and can you track this CPM over time?
- What is the potential revenue range our publisher can make in July?
Importing the required libraries¶
In [1]:
import os
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler, FunctionTransformer
from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score
from sklearn import tree
from sklearn.linear_model import LinearRegression
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.graph_objs as go
from plotly.offline import init_notebook_mode, iplot, plot
import graphviz
import seaborn as sns
import math
In [2]:
import matplotlib.pyplot as plt
from matplotlib import pyplot
import seaborn as sns
import plotly.graph_objs as go
from plotly.offline import init_notebook_mode, iplot, plot
from scipy.stats.mstats import mode
%matplotlib inline
from matplotlib.pylab import rcParams
rcParams['figure.figsize'] = 12, 4
Reading and loading the dataset¶
In [3]:
data = pd.read_csv('Ascendeum_Dataset2.csv')
data.head()
Out[3]:
In [4]:
data.tail()
Out[4]:
Understanding the data¶
In [5]:
data.shape
Out[5]:
In [6]:
data.nunique()
Out[6]:
In [7]:
data.dtypes
Out[7]:
In [8]:
data.info()
In [9]:
data.describe()
Out[9]:
In [10]:
data.describe(include = 'object')
Out[10]:
Checking for missing values¶
In [11]:
data.isnull().sum()
Out[11]:
We understand that there are no missing values in the dataset and continue our analysis
Overview of the data¶
In [12]:
#!pip install pandas-profiling[notebook,html]
In [13]:
import pandas_profiling
In [14]:
pandas_profiling.ProfileReport(data)
Out[14]:
We get a detailed report and insights on the entire dataset including the features - categorical & numerical, missing values, correlation between different numerical features, unique/distinct values, and a sample of the dataset (first few & last few rows).
Exploratory Data Analysis & Visualization¶
Creation of a plots for numerical variables in plotly¶
Question 1:
The box plot helps us identify outliers/inconsistencies in the dataset w.r.t impressions and revenue in distribution and measure variablity in the dataset.
In [15]:
def summary(x):
x_min = data[x].min()
x_max = data[x].max()
Q1 = data[x].quantile(0.25)
Q2 = data[x].quantile(0.50)
Q3 = data[x].quantile(0.75)
print(f'5 Point Summary of {x.capitalize()} Attribute:\n'
f'{x.capitalize()}(min) : {x_min}\n'
f'Q1 : {Q1}\n'
f'Q2(Median) : {Q2}\n'
f'Q3 : {Q3}\n'
f'{x.capitalize()}(max) : {x_max}')
fig = plt.figure(figsize=(16, 10))
plt.subplots_adjust(hspace = 0.6)
sns.set_palette('pastel')
plt.subplot(221)
ax1 = sns.distplot(data[x], color = 'r')
plt.title(f'{x.capitalize()} Density Distribution')
plt.subplot(222)
ax2 = sns.violinplot(x = data[x], palette = 'Accent', split = True)
plt.title(f'{x.capitalize()} Violinplot')
plt.subplot(223)
ax2 = sns.boxplot(x=data[x], palette = 'cool', width=0.7, linewidth=0.6)
plt.title(f'{x.capitalize()} Boxplot')
plt.subplot(224)
ax3 = sns.kdeplot(data[x], cumulative=True)
plt.title(f'{x.capitalize()} Cumulative Density Distribution')
plt.show()
In [16]:
def box_plot(x = 'bmi'):
def add_values(bp, ax):
""" This actually adds the numbers to the various points of the boxplots"""
for element in ['whiskers', 'medians', 'caps']:
for line in bp[element]:
# Get the position of the element. y is the label you want
(x_l, y),(x_r, _) = line.get_xydata()
# Make sure datapoints exist
# (I've been working with intervals, should not be problem for this case)
if not np.isnan(y):
x_line_center = x_l + (x_r - x_l)/2
y_line_center = y # Since it's a line and it's horisontal
# overlay the value: on the line, from center to right
ax.text(x_line_center, y_line_center, # Position
'%.2f' % y, # Value (3f = 3 decimal float)
verticalalignment='center', # Centered vertically with line
fontsize=12, backgroundcolor="white")
fig, axes = plt.subplots(1, figsize=(4, 8))
red_diamond = dict(markerfacecolor='r', marker='D')
bp_dict = data.boxplot(column = x,
grid=True,
figsize=(4, 8),
ax=axes,
vert = True,
notch=False,
widths = 0.7,
showmeans = True,
whis = 1.5,
flierprops = red_diamond,
boxprops= dict(linewidth=3.0, color='black'),
whiskerprops=dict(linewidth=3.0, color='black'),
return_type = 'dict')
add_values(bp_dict, axes)
plt.title(f'{x.capitalize()} Boxplot', fontsize=16)
plt.ylabel(f'{x.capitalize()}', fontsize=14)
plt.show()
skew = data[x].skew()
Q1 = data[x].quantile(0.25)
Q3 = data[x].quantile(0.75)
IQR = Q3 - Q1
total_outlier_num = ((data[x] < (Q1 - 1.5 * IQR)) | (data[x] > (Q3 + 1.5 * IQR))).sum()
print(f'Mean {x.capitalize()} = {data[x].mean()}')
print(f'Median {x.capitalize()} = {data[x].median()}')
print(f'Skewness of {x}: {skew}.')
print(f'Total number of outliers in {x} distribution: {total_outlier_num}.')
1. total_revenue¶
In [17]:
summary('total_revenue')
In [18]:
box_plot('total_revenue')
2. total_impressions¶
In [19]:
box_plot('total_impressions')
3. viewable_impressions¶
In [20]:
box_plot('viewable_impressions')
4. measurable_impressions¶
In [21]:
box_plot('measurable_impressions')
5. revenue_share_percent¶
In [22]:
box_plot('revenue_share_percent')
CPM – cost per Mille.¶
This is one of the most important ways to measure performance. It is calculated as:
CPM = revenue/impressions * 1000.
Question 2: Tracking and understanding the advertiser that has the highest CPM
In [23]:
data['CPM']=data['total_revenue']*1000
In [24]:
data.head()
Out[24]:
In [25]:
data.tail()
Out[25]:
In [26]:
box_plot('CPM')
In [27]:
sns.distplot(data['CPM'])
plt.show()
In [28]:
data.dtypes
Out[28]:
In [29]:
data.isnull().sum()
Out[29]:
In [30]:
data.CPM.fillna(data.CPM.max(),inplace =True)
Binning the CPM value to understand the advertiser with highest CPM
In [31]:
bins = [0,3,5,7,9]
labels = ["low","medium","high","very-high"]
data['Binned_CPM'] = pd.cut(data['CPM'], bins=bins, labels=labels)
data['Binned_CPM'] = pd.cut(data['CPM'], bins=bins, labels=labels)
In [32]:
data.CPM.dtypes
Out[32]:
In [33]:
data.isnull().sum()
Out[33]:
In [34]:
sns.countplot(x='Binned_CPM',data = data)
plt.show()
data.CPM.value_counts(normalize = True)*100
Out[34]:
As per the plot we understand that the advertiser with maximum CPM falls in the first category of the Binned_CPM range
Converting date column for further analysis¶
In [35]:
data["date"] = data["date"].astype('datetime64[ns]')
In [36]:
data[['day','month','year']] = data.date.apply(lambda x: pd.Series(x.strftime("%d,%m,%Y").split(",")))
In [37]:
data.head()
Out[37]:
In [38]:
data.dtypes
Out[38]:
In [39]:
data["month"] = data["month"].astype('category')
In [40]:
g = sns.FacetGrid(data, col='month')
g.map(plt.hist, 'Binned_CPM', bins=20)
Out[40]:
